package model;

import helperclasses.DbUtil;
import helperclasses.Product;
import helperclasses.SalesRecord;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.sql.DataSource;

public class ModifySales {
	private File file;
	BufferedReader br;
	Connection conn;
	Statement st;
	ResultSet result;
	HashMap<String, Product> productMap;

	public ModifySales() {
		productMap = new HashMap<String, Product>();
		openConnection();

	}

	private void openConnection() {
		try {
			conn = getConnection();
			st = conn.createStatement();
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private void closeConnection() {
		try {
			st.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	private Connection getConnection() throws SQLException {
		DataSource ds = DbUtil.getInstance().getDataSource();
		Connection conn = ds.getConnection();
		return conn;
	}

	public boolean ReadFile() {
		boolean succes = false;
		FileInputStream fstream;
		try {
			fstream = new FileInputStream(getFile().getAbsolutePath());
			// Get the object of DataInputStream
			DataInputStream in = new DataInputStream(fstream);
			br = new BufferedReader(new InputStreamReader(in));
			String strLine;
			// Read File Line By Line
			while ((strLine = br.readLine()) != null) {
				// Print the content on the console
				parseLine(strLine);
			}
			conn.commit();
			closeConnection();
			succes = true;
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			try {
				conn.rollback();
				succes = false;
			} catch (SQLException e1) {
				e1.printStackTrace();
			} finally {
				closeConnection();
			}
			e.printStackTrace();
		}
		return succes;
	}

	private void parseLine(String strLine) throws SQLException {
		String[] firstSplit, secondSplit, replaceSplit, dateSplit;
		String half, newDate;
		SalesRecord salesRecord = new SalesRecord();
		firstSplit = strLine.split("VALUES");
		half = firstSplit[1];
		secondSplit = half.split(",");
		replaceSplit = new String[secondSplit.length];
		for (int i = 0; i < secondSplit.length; i++) {
			replaceSplit[i] = secondSplit[i].replace("\'", "");
		}
		replaceSplit[0] = replaceSplit[0].replace("(", "");
		replaceSplit[0] = replaceSplit[0].replace(" ", "");
		// alter date format
		dateSplit = replaceSplit[0].split("/");
		newDate = dateSplit[2] + "-" + dateSplit[1] + "-" + dateSplit[0];
		replaceSplit[0] = newDate;
		replaceSplit[replaceSplit.length - 1] = replaceSplit[replaceSplit.length - 1].replace(")", "");
		replaceSplit[replaceSplit.length - 1] = replaceSplit[replaceSplit.length - 1].replace(" ", "");
		replaceSplit[replaceSplit.length - 1] = replaceSplit[replaceSplit.length - 1].replace("#", "");
		replaceSplit[replaceSplit.length - 1] = replaceSplit[replaceSplit.length - 1].replace(";", "");
		Product produs = new Product();

		salesRecord.setData_nota(replaceSplit[0]);
		produs.setProductName(replaceSplit[2]);
		salesRecord.setCantitate(Integer.parseInt(replaceSplit[3]));
		salesRecord.setOra(replaceSplit[6] + ":00");
		salesRecord.setNotaId(Integer.parseInt(replaceSplit[7]));
		salesRecord.setSectie(replaceSplit[9]);
		salesRecord.setNumeMasa(replaceSplit[12]);
		salesRecord.setNumeOspatar(replaceSplit[13]);
		salesRecord.setModPlata(replaceSplit[14]);
		salesRecord.setDiscount(Double.parseDouble(replaceSplit[19]));
		salesRecord.setProdus(produs);

		InsertRecord(salesRecord);
	}

	private void InsertRecord(SalesRecord salesRecord) throws SQLException {
		String sql;
		ResultSet result;
		// Product product = new Product();

		sql = "SELECT prod.produs_id, prod.valoare, prod.pret_iesire, prod.tip_nir, ps.cantitate, ps.valoare_in_stoc FROM produse prod NATURAL JOIN produse_in_stoc ps WHERE nume='"
				+ salesRecord.getProdus().getProductName() + "'";
		result = st.executeQuery(sql);
		while (result.next()) {
			// salesRecord.getProdus().setProductName(salesRecord.getNumeProdus());
			salesRecord.getProdus().setProductId(Integer.parseInt(result.getString("produs_id")));
			salesRecord.getProdus().setTipNir(Integer.parseInt(result.getString("tip_nir")));
			salesRecord.getProdus().setValoare(Double.parseDouble(result.getString("valoare")));
			salesRecord.getProdus().setCantitate(Integer.parseInt(result.getString("cantitate")));
			salesRecord.getProdus().setPretIesire(Double.parseDouble(result.getString("pret_iesire")));
			salesRecord.getProdus().setValoareInStoc(Double.parseDouble(result.getString("valoare_in_stoc")));
		}

		sql = "INSERT INTO sales (sales_id, data_nota, ora, nota_id, nume_masa, nume_ospatar, mod_plata, sectie, cantitate, pret_iesire, discount, produs_id_sales) VALUES(null, '"
				+ salesRecord.getData_nota()
				+ "', '"
				+ salesRecord.getOra()
				+ "', "
				+ salesRecord.getNotaId()
				+ ", '"
				+ salesRecord.getNumeMasa()
				+ "', '"
				+ salesRecord.getNumeOspatar()
				+ "', '"
				+ salesRecord.getModPlata()
				+ "', '"
				+ salesRecord.getSectie()
				+ "', "
				+ salesRecord.getCantitate()
				+ ", "
				+ salesRecord.getProdus().getPretIesire()
				+ ", " + salesRecord.getDiscount() + ", " + salesRecord.getProdus().getProductId() + ")";

		st.execute(sql);

		// Alter stock
		alterStock(salesRecord);

	}

	private void alterStock(SalesRecord salesRecord) throws SQLException {
		String sql;
		if (salesRecord.getProdus().getTipNir() == 1 || salesRecord.getProdus().getTipNir() == 4) {
			// we just update the cantitate field
			double cantitate;
			cantitate = Math.round((salesRecord.getProdus().getCantitate() - salesRecord.getCantitate()) * 10000) / 10000;
			sql = "UPDATE produse_in_stoc SET cantitate=" + cantitate + ", valoare_in_stoc=valoare_in_stoc-("
					+ salesRecord.getCantitate() + " * (SELECT valoare FROM produse WHERE produs_id="
					+ salesRecord.getProdus().getProductId() + " ) )  WHERE produs_id=" + salesRecord.getProdus().getProductId();
			st.executeUpdate(sql);
		} else if (salesRecord.getProdus().getTipNir() == 3) {
			// this is a composed product, we have to alter each component

			// 1. we must get the ingredients IDsfrom the DB
			sql = "SELECT produs_id_retetar, valoare FROM retetar WHERE cocktail_id=" + salesRecord.getProdus().getProductId();
			result = st.executeQuery(sql);
			List<Integer> ingredientId = new ArrayList<Integer>();
			List<Double> valoareIngredientRetetar = new ArrayList<Double>();
			while (result.next()) {
				ingredientId.add(Integer.parseInt(result.getString("produs_id_retetar")));
				valoareIngredientRetetar.add(Double.parseDouble(result.getString("valoare")));
			}

			for (int i = 0; i < ingredientId.size(); i++) {
				// 2. we go trough each ingredient and we alter valoare_in_stoc
				// and cantitate fields according with the sales
				sql = "SELECT prod.valoare, ps.cantitate, ps.valoare_in_stoc FROM produse prod NATURAL JOIN produse_in_stoc ps WHERE produs_id="
						+ ingredientId.get(i);
				result = st.executeQuery(sql);
				result.next();
				double newValoareInStoc = 0;
				double oldValoareInStoc = Double.parseDouble(result.getString("valoare_in_stoc"));
				int cantitate = Integer.parseInt(result.getString("cantitate"));
				double valoare = Double.parseDouble(result.getString("valoare"));
				double valoareVanduta = 0;
				int cantitateNoua = 0;

				valoareVanduta = Math.round((valoareIngredientRetetar.get(i) * salesRecord.getCantitate()) * 10000) / 10000.0;
				newValoareInStoc = Math.round((oldValoareInStoc - valoareVanduta) * 10000) / 10000.0;
				cantitateNoua = (int) (newValoareInStoc / valoare);

				sql = "UPDATE produse_in_stoc SET cantitate=" + cantitateNoua + ", valoare_in_stoc=" + newValoareInStoc
						+ " WHERE produs_id=" + ingredientId.get(i);
				st.executeUpdate(sql);
			}
		}
	}

	public File getFile() {
		return file;
	}

	public void setFile(File file) {
		this.file = file;
	}

}
